package com.hkts.naturalgas.weight.service.serviceimpl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.hkts.naturalgas.utils.Result;
import com.hkts.naturalgas.utils.ResultUtil;
import com.hkts.naturalgas.weight.entity.WeightTableItem;
import com.hkts.naturalgas.weight.entity.WeightTables;
import com.hkts.naturalgas.weight.mapper.WeightTableItemMapper;
import com.hkts.naturalgas.weight.mapper.WeightTablesMapper;
import com.hkts.naturalgas.weight.service.IWeightTablesService;
import com.hkts.naturalgas.weight.vo.*;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.Date;
import java.util.*;
import java.util.stream.Collectors;

/**
 * <p>
 * 用于维护称重计量的表 服务实现类
 * </p>
 *
 * @author yck
 * @since 2022-06-13
 */
@Service
@Transactional
@Slf4j
public class WeightTablesServiceImpl extends ServiceImpl<WeightTablesMapper, WeightTables> implements IWeightTablesService {


    @Autowired
    private WeightTablesMapper weightTablesMapper;
    @Autowired
    private WeightTableItemMapper weightTableItemMapper;

    @Override
    public IPage<WeightTables> selectPage(Page<WeightTables> page, WeightTables weightTables) {
        QueryWrapper<WeightTables> query = Wrappers.query();

        return weightTablesMapper.selectPage(page, query);
    }

    @Override
    public List<WeightTables> selectList(WeightTables weightTables) {
        QueryWrapper<WeightTables> query = Wrappers.query();

        return weightTablesMapper.selectList(query);
    }

    @Override
    public Result getWeightList(int id, String date) throws ParseException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat formatS = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        WeightTables weightTables = weightTablesMapper.selectById(id);
        Date date1 = new Date();
        String format1 = format.format(date1);
        Date startTime = null;
        Date endTime = null;
        if (StringUtils.isNotBlank(date) && !format1.equals(date)) {
            startTime = getTime(date, 7);
            endTime = getTime(date, 31);
        } else {
            int hours = date1.getHours();
            String format2 = format.format(date1);
            if (hours >= 7) {
                //计算开始时间
                startTime = getTime(format2, 7);
                endTime = date1;
            } else {
                Date date2 = getDate(format2, -1);
                String format3 = format.format(date2);
                startTime = getTime(format3, 7);
                endTime = date1;
            }
        }

        ResultVo data = getWeightData(weightTables, formatS.format(startTime), formatS.format(endTime));
        DataResult result1 = new DataResult();
        if (data.getCode().equals("500")) {
            weightTables.setUrlState("2");
            weightTables.setInvalidTime(LocalDateTime.now());
            weightTablesMapper.updateById(weightTables);
            return ResultUtil.error("数据库链接失败，请检查网络是否正常");
        } else {
            List<WeightVo> result = data.getResult();
            for (int i = 0; i < result.size(); i++) {
                WeightVo weightVo = result.get(i);
                Date parse = formatS.parse(result.get(i).getDischargingTime());
                if (parse.getHours() >= 7 && parse.getHours() <= 19) {
                    weightVo.setShift("白班");
                    weightVo.setSerialNumber(result1.getDayShiftData().size() + 1);
                    result1.getDayShiftData().add(weightVo);
                } else {
                    weightVo.setShift("夜班");
                    weightVo.setSerialNumber(result1.getNightShiftData().size() + 1);
                    result1.getNightShiftData().add(weightVo);
                }
            }

        }
        return ResultUtil.data(result1);
    }

    @Override
    public Result getDataList(int id, String date) throws ParseException {
        DataResultVo vo = new DataResultVo();
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat formatS = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        WeightTables weightTables = weightTablesMapper.selectById(id);
        Date date1 = new Date();
        String format1 = format.format(date1);
        Date startTime = null;
        Date endTime = null;
        if (StringUtils.isNotBlank(date) && !format1.equals(date)) {
            startTime = getTime(date, 7);
            endTime = getTime(date, 31);
        } else {
            int hours = date1.getHours();
            String format2 = format.format(date1);
            if (hours >= 7) {
                //计算开始时间
                startTime = getTime(format2, 7);
                endTime = date1;
            } else {
                Date date2 = getDate(format2, -1);
                String format3 = format.format(date2);
                startTime = getTime(format3, 7);
                endTime = date1;
            }
        }
        QueryWrapper<WeightTableItem> query = Wrappers.query();
        query.lambda().eq(WeightTableItem::getWeightTableId, weightTables.getId());
        query.lambda().orderByAsc(WeightTableItem::getFieldOrder);
        //所有字段
        List<WeightTableItem> weightTableItems = weightTableItemMapper.selectList(query);
        ResultMapVo data = getData(weightTables, weightTableItems, formatS.format(startTime), formatS.format(endTime));
        if (data.getCode().equals("500")) {
            weightTables.setUrlState("2");
            weightTables.setInvalidTime(LocalDateTime.now());
            weightTablesMapper.updateById(weightTables);
            return ResultUtil.error("数据库链接失败，请检查网络是否正常");
        } else {
            List<Map> result = data.getResult();
            WeightTableItem item = new WeightTableItem();
            for (int i = 0; i < weightTableItems.size(); i++) {
                if (StringUtils.isNotBlank(weightTableItems.get(i).getTableField())
                        &&weightTableItems.get(i).getTableField().equals("datetime")) {
                    item = weightTableItems.get(i);
                    break;
                }
            }
            for (int i = 0; i < result.size(); i++) {
                Map map = result.get(i);
                Date parse = formatS.parse(map.get(item.getJsonField()).toString());
                if (parse.getHours() >= 7 && parse.getHours() <= 19) {
                    map.put("shift", "白班");
                    map.put("serialNumber", vo.getDayShiftData().size() + 1);
                    vo.getDayShiftData().add(map);
                } else {
                    map.put("shift", "夜班");
                    map.put("serialNumber", vo.getNightShiftData().size() + 1);
                    vo.getNightShiftData().add(map);
                }
            }
        }
        return ResultUtil.data(vo);
    }

    public ResultVo getWeightData(WeightTables weightTables, String startTime, String endTime) {
        ResultVo resultVo = new ResultVo();
        String URL = "jdbc:mysql://" + weightTables.getUrl() + "?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT";
        String USER = weightTables.getUsername();
        String PASSWORD = weightTables.getPassword();
        Connection conn = null;
        Statement st = null;
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        //1.加载驱动程序
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            //设置超时时间
            DriverManager.setLoginTimeout(1);
            //2.获得数据库链接
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            st = conn.createStatement();
            //3.通过数据库的连接操作数据库，实现增删改查（使用Statement类）
            String sql = "";
            sql = "SELECT * from " + weightTables.getTableName() + " WHERE weight>0 and datetime>'" + startTime + "' and datetime<='" + endTime + "'  ORDER BY   datetime  ";
            log.info("放料查询sql:  {}", sql);
            ResultSet rs = st.executeQuery(sql);

            List<WeightVo> list = new ArrayList<WeightVo>();
            while (rs.next()) {
                WeightVo vo = new WeightVo();
                int weight = rs.getInt("weight");
                vo.setDischargeQuantity(weight);
                Date date = rs.getDate("date");
                vo.setDate(dateFormat.format(date));
                String datetime = rs.getString("datetime");
                vo.setDischargingTime(datetime);
                vo.setSerialNumber(list.size() + 1);
                list.add(vo);
            }
            resultVo.setCode("200");
            resultVo.setResult(list);
            rs.close();
        } catch (Exception e) {
            log.error(e.getMessage());
            resultVo.setCode("500");
            e.printStackTrace();
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        //4.处理数据库的返回结果(使用ResultSet类)
        //关闭资源
        return resultVo;
    }

    public ResultMapVo getData(WeightTables weightTables, List<WeightTableItem> weightTableItems, String startTime, String endTime) {
        ResultMapVo resultVo = new ResultMapVo();
        String URL = "jdbc:mysql://" + weightTables.getUrl() + "?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT";
        String USER = weightTables.getUsername();
        String PASSWORD = weightTables.getPassword();
        Connection conn = null;
        Statement st = null;
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        ResultSet rs = null;


        List<WeightTableItem> collect = weightTableItems.stream()
                .filter(weightTableItem -> weightTableItem.getIsSum() == 1)
                .collect(Collectors.toList());


        //1.加载驱动程序
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            //设置超时时间
            DriverManager.setLoginTimeout(1);
            //2.获得数据库链接
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            st = conn.createStatement();
            //3.通过数据库的连接操作数据库，实现增删改查（使用Statement类）
            StringBuffer sqlBuffer = new StringBuffer();
            sqlBuffer.append("SELECT * from ").append(weightTables.getTableName())
                    .append(" WHERE   datetime>'" + startTime + "' and datetime<='" + endTime + "' ");
            for (int i = 0; i < collect.size(); i++) {
                sqlBuffer.append(" and ").append(collect.get(i).getTableField()).append(" > 0 ");
            }
            sqlBuffer.append(" ORDER BY   datetime");
            log.info("查询sql:  {}", sqlBuffer.toString());
            rs = st.executeQuery(sqlBuffer.toString());
            List<Map> list = new ArrayList<Map>();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 0; i < weightTableItems.size(); i++) {
                    WeightTableItem weightTableItem = weightTableItems.get(i);
                    if (weightTableItem.getFieldType().equals("string")) {
                        map.put(weightTableItem.getJsonField(), rs.getString(weightTableItem.getTableField()));
                    } else if (weightTableItem.getFieldType().equals("int")) {
                        map.put(weightTableItem.getJsonField(), rs.getInt(weightTableItem.getTableField()));
                    }

                }
                list.add(map);
            }
            resultVo.setCode("200");
            resultVo.setResult(list);
            rs.close();
        } catch (Exception e) {
            log.error(e.getMessage());
            resultVo.setCode("500");
            e.printStackTrace();
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        //4.处理数据库的返回结果(使用ResultSet类)
        //关闭资源

        return resultVo;
    }


    public Date getTime(String date, int num) {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat formatH = new SimpleDateFormat("HH");
        SimpleDateFormat formatS = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date time = null;
        try {
            Date parse = format.parse(date);
            Calendar dar = Calendar.getInstance();
            dar.setTime(parse);
            dar.add(Calendar.HOUR, num);
            time = dar.getTime();

        } catch (ParseException e) {
            e.printStackTrace();
        }
        return time;
    }

    public Date getDate(String date, int num) {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat formatH = new SimpleDateFormat("HH");
        SimpleDateFormat formatS = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date time = null;
        try {
            Date parse = format.parse(date);
            Calendar dar = Calendar.getInstance();
            dar.setTime(parse);
            dar.add(Calendar.DATE, num);
            time = dar.getTime();

        } catch (ParseException e) {
            e.printStackTrace();
        }
        return time;
    }
}
